var express = require('express');
var fs = require('fs');
var path = require('path');
var router = express.Router();
var session = require('express-session');
router.use(session({secret: 'keyboard cat', resave: false, saveUninitialized: true, cookie: {maxAge: 60000}}));

// 导入MySQL模块
var mysql = require('mysql');

var dbConfig =  {  
    mysql: {   
        host: '192.168.2.200',
        user: 'root',
        port: '4406',
        password : '123456',
        database : 'expressdb'
    }
 }

 var userSQL = {  
    findUser:'SELECT * FROM user WHERE name = ?',
    insert:'INSERT INTO user(name,password) VALUES(?,?)', 
    queryAll:'SELECT * FROM user',  
    getUserById:'SELECT * FROM user WHERE name = ? ',
    test:'SELECT * FROM user',
    login:'SELECT (name,password) FROM user WHERE VALUES(?,?) '
}

var blogSQL = {
    //查询博客下的评论条数和点赞条数
    findAllBlogAndCommit:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blog.showIndex = 1 order by creat_time DESC LIMIT ?,?' ,
    findAllBlog:'SELECT `blog`.* ,`blog_class`.className FROM `blog` LEFT JOIN `blog_class` ON `blog`.blogId = `blog_class`.blogId',//多表关联查询 左连接 查询包含每条博客的标签
    findAllBlogByclassName:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blog.showIndex = 1 and className = ?order by creat_time DESC LIMIT ?,?',
    addBlog:'INSERT INTO blog(userName,title,body,className,creat_time,update_time) VALUES(?,?,?,?,?,?)',
    updateBlog:'UPDATE blog SET title = ?,body = ?,className=?,update_time=? WHERE blogId = ?',
    deleteBlog:'DELETE FROM blog WHERE blogId = ?',
    findBlogByUsername:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog WHERE userName = ? and blog.showIndex = 1 order by update_time DESC',
    addBlogClass:'UPDATE blog SET className = ? WHERE blogId = ?',
    findBlogClass:'SELECT DISTINCT className FROM `blog` where showIndex = 1',
    findBlogByClassName:'SELECT * FROM blog WHERE className = ?',
    findBlogCommitByBlogId:'SELECT * from user as a LEFT JOIN blog_commit as b on a.name = b.commitName where blogId = ? order by update_time DESC',
    findBlogByBlogId:'select blog.* , (select COUNT(blogId) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCount , (select COUNT(blogId) from blog_fabulous where blog_fabulous.blogId = blog.blogId) as fabulousCount from blog where blogId = ?',
    addCommit:'INSERT INTO blog_commit(commitName,blogId,commitBody,avatar,creat_time) VALUES (?,?,?,?,?)',
    changeHeadImg:'UPDATE user SET headImg = ? WHERE `user`.name = ? and `user`.`password`= ?',
    //查找所有博客评论条数 :'select blog.* , (select count(id) from blog_commit where blog_commit.blogId = blog.blogId) as reviewsCountfrom blogorder by blog.blogId)'
    updatedUserInfo:'UPDATE user SET nickName = ?,info = ?,sex = ? WHERE name= ? and password = ?',
    findFabByUsername:'select * from blog as a LEFT JOIN blog_fabulous as b on a.blogId = b.blogId where commitName = ?'
}

// 使用DBConfig.js的配置信息创建一个MySQL连接池
var pool = mysql.createPool( dbConfig.mysql );
// 响应一个JSON数据

router.get('/', function(req, res, next) {
  res.render('index', { title: 'Express' });
});

//注册接口
router.post('/register', function(req, res, next){//name,password
    // 获取前台页面传过来的参数  
    var param = req.body;   
    // 建立连接 增加一个用户信息 
    pool.getConnection(function(err, connection) {
        connection.query(userSQL.findUser,[param.name],function (err,result) {
             // 以json形式，把操作结果返回给前台页面     
            if(result==false){
                connection.query(userSQL.insert,[param.name,param.password], function(err, result) {
                    if(result) {      
                        result = {   
                            code: 200,   
                            msg:'增加成功'
                        };  
                    }     
                // 以json形式，把操作结果返回给前台页面     
                res.send({success:true,data:result});
                connection.release()
                // 释放连接 如果不释放连接超过数据库最大连接数量就会卡死
                });
            }else{
                res.send({success:false,errorMsg:'该用户存在表中'});
                connection.release()
            }
        })
    })
})

//登录
router.post('/login', function(req, res, next){//name password
    // 获取前台页面传过来的参数  req.query
    var param = req.body;   
    console.log(req.body)
    // 建立连接 增加一个用户信息 
    pool.getConnection(function(err, connection) {
        connection.query(userSQL.findUser,[param.name],function (err,result) {
             // 以json形式，把操作结果返回给前台页面 
            console.log(result)    
            if(result==false){ //数组为空
                res.send({success:false,errorMsg:'该用户未注册'});
                connection.release()
            }else{
                if(result[0].password == param.password){
                    req.session.userInfo = result[0].name
                    res.send({success:true,data:'登录成功',userInfo:result[0]})
                    connection.release()
                }else{
                    res.send({success:false,data:'用户名或密码错误'})
                    connection.release()
                }
            }
        })
    })
})

//检查是否登录
router.get('/islogin', function(req, res, next){//name
    // 获取前台页面传过来的参数  
    var param = req.query;   
    // 建立连接 增加一个用户信息 
    console.log(req.session.userInfo)
    if(req.session.userInfo == param.name){
        res.send({success:true,data:req.session.userInfo,msg:'已登录'});
    }else{
        res.send({success:false,data:'未登录'});
        
    }
})

//注销
router.post('/logout', function(req, res, next){
    // 获取前台页面传过来的参数  
    var param = req.query;   
    req.session.destroy();
    res.send({success:true,data:'注销成功'});
    connection.release()
})

//添加博客
router.post('/addBlog', function(req, res, next){//name,title,body
    var param = req.body;
    var date = new Date()
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.addBlog,[param.name,param.title,param.body,param.className,date,date],function (err,result) {
            if(err){
                res.send({success:true,data:err,msg:'添加失败'});
                connection.release()
            }
            res.send({success:true,data:'添加成功'});
            connection.release()
        })
    })
})

//删除博客
router.post('/deleteBlog', function(req, res, next){ //blogId
    var param = req.query;
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.deleteBlog,[param.blogId],function (err,result) {
            if(err){
                res.send({success:true,data:err,msg:'添加失败'});
                connection.release()
            }
            res.send({success:true,data:'删除成功'});
            connection.release()
        })
    })
})

//更改博客
router.post('/updateBlog', function(req, res, next){ //title,body,blogId
    var param = req.body;
    console.log(param.update_time)
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.updateBlog,[param.title,param.body,param.className,param.update_time,param.blogId],function (err,result) {
            if(err){
                res.send({success:true,data:err,msg:'添加失败'});
                connection.release()
            }
            res.send({success:true,data:'更改成功'});
            connection.release()
        })
    })
})

//查某人的博客
router.get('/findBlogByUsername', function(req, res, next){ //username
    var param = req.query;
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findBlogByUsername,[param.userName],function (err,result) { 
            if(result==false){
                res.send({success:false,data:'',errorMsg:'无博客'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})

//添加博客的分类名
router.post('/addBlogClass', function(req, res, next){ //username
    var param = req.query;
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.addBlogClass,[param.className,param.blogId],function (err,result) {
            if(err){
                res.send({success:true,data:err,msg:'添加失败'});
                connection.release()
            }else{
                res.send({success:true,data:'添加成功'});
                connection.release()
            }
            
        })
    })
})

//查找所有博客(包括评论条数和点赞数)
router.get('/findAllBlogAndCommit', function(req, res, next){ //username
    var param = req.query;
    var row =  parseInt(param.row) 
    var page = (param.page-1) * row
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findAllBlogAndCommit,[page,row],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查询失败'});
                connection.release()
            }else{
                connection.query('SELECT COUNT(blogId) from blog where showIndex = 1',function (err,result2) {
                    res.send({success:true,data:result,total:result2[0]['COUNT(blogId)']});
                    connection.release()
                })
            }
        })
    })
})

//查找同一分类的博客
router.get('/findAllBlogByclassName', function(req, res, next){ //className page row
    var param = req.query;
    var row =  parseInt(param.row) 
    var page = (param.page-1) * row
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findAllBlogByclassName,[param.className,page,row],function (err,result) {
            res.send({success:true,data:result,total:result.length});
            connection.release()
        })
    })
})


//通过id查找博客
router.get('/findBlogByBlogId', function(req, res, next){ //username
    var param = req.query;
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findBlogByBlogId,[param.blogId],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查找'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})
//查找所有博客分类
router.get('/findBlogClass', function(req, res, next){ //username
    var param = req.query;
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findBlogClass,function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查找失败'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})
//查找博客分类下的所有博客
router.post('/findBlogByClassName', function(req, res, next){ //username
    var param = req.body;//放到body里面
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findBlogByClassName,[param.className],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查找失败'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})
//增加阅读量 含有过滤ip
router.post('/addReadCount', function(req, res, next){ //blogId
    var param = req.body;//放到body里面
    console.log(param, req.body)
    let getClientIp = function (req) {
        return req.headers['x-forwarded-for'] || req.connection.remoteAddress || req.socket.remoteAddress || req.connection.socket.remoteAddress || '';
    };
    
    console.log(getClientIp(req));
    let ip = getClientIp(req).match(/\d+.\d+.\d+.\d+/);
    console.log(ip);
    ip = ip ? ip.join('.') : null;
    console.log(ip);


    pool.getConnection(function(err, connection) {
        connection.query("SElECT ip FROM blog_readIp WHERE blogId = ?",[param.blogId],function (err,result) {
            var exit = false
            console.log(result)
            if(result.length==0){
                exit = false
            }else{
                result.forEach(item=>{
                    if(item.ip==ip){
                        exit = true
                    }else{
                        exit = false
                    }
                })
            }

            if(exit){
                res.send({success:false,data:err,msg:'改博客已被改ip查看过一次'});
                connection.release()
            }else{
                connection.query("INSERT INTO blog_readIp(ip,blogId) VALUES (?,?)",[ip,param.blogId],function (err,result1) {
                    if(err){
                        //console.log('插入失败11111111111');
                        res.send({success:false,data:err,msg:'插入失败1'});
                        connection.release()
                    }else{
                        connection.query("UPDATE blog SET readcount = readcount+1 WHERE blogId = ?",[param.blogId],function (err,result2) {
                            if(err){
                                //console.log('插入失败222222222');
                                res.send({success:false,data:err,msg:'插入失败2'});
                                connection.release()
                            }else{
                                //console.log('插入成功!!!!!!!!!!!!!!');
                                res.send({success:true,data:result2,msg:'插入成功'});
                                connection.release()
                            }
                        })
                    }
                })
            }
        })
    })
})

//增加点赞数 含有过滤用户 参数blogId userName
router.post('/addFabulous', function(req, res, next){ //username
    var param = req.body;//放到body里面
    pool.getConnection(function(err, connection) {
        connection.query('select blogId from blog_fabulous where commitName = ?',[param.userName],function (err,result) {
            console.log(result,result == false)
                var exit 
                result.forEach(item=>{
                    console.log(item.blogId)
                    if(item.blogId==param.blogId){
                        exit = true
                        
                    }else{
                        exit = false
                    }
                })
                if(!exit){
                    connection.query('INSERT INTO blog_fabulous(commitName,blogId) VALUES(?,?)',[param.userName,param.blogId],function (err,result) {
                        if(err) {
                            res.send({success:false,data:err,msg:'点赞失败2'})
                            connection.release()
                        }
                        else {
                            res.send({success:true,data:'点赞成功!'});
                            connection.release()
                        }
                    })
                }else{
                    res.send({success:false,data:err,msg:'已经点赞过了,不要重复点赞'});
                    connection.release()
                }
        })
    })
})

//取消对某个博客的点赞
router.post('/cancelFabulous', function(req, res, next){ //username
    var param = req.body;//放到body里面
    pool.getConnection(function(err, connection) {
        connection.query('DELETE FROM blog_fabulous WHERE blogId = ? AND commitName = ?',[param.blogId,param.userName],function (err,result) {
            if(err){
                    res.send({success:false,data:err,msg:'点赞失败'})
                    connection.release()
            }else{
                res.send({success:true,data:'',msg:'取消成功'});
                connection.release()
            }
        })
    })
    
})

//查找该博客下所有评论
router.get('/findBlogCommitByBlogId', function(req, res, next){ //username
    var param = req.query;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findBlogCommitByBlogId,[param.blogId],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查找失败'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})

//查找某人收藏的博客
router.get('/findFabByUsername', function(req, res, next){ //username
    var param = req.query;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.findFabByUsername,[param.userName],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'查找失败'});
                connection.release()
            }else{
                res.send({success:true,data:result});
                connection.release()
            }
        })
    })
})

//增加评论
router.post('/addCommit', function(req, res, next){ //commitName,blogId,commitBody,avatar
    var param = req.body;
    console.log(param, req.body)
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.addCommit,[param.commitName,param.blogId,param.commitBody,param.avatar,new Date()],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'评论失败'});
                connection.release()
            }else{
                res.send({success:true,data:'评论成功'});
                connection.release()
            }
        })
    })
})


//上传图片接口 头像
router.post('/uploadImg', function(req, res, next){ //username
    var param = req.body;
    var imgData = req.body.imgData;
    var base64Data = imgData.replace(/^data:image\/\w+;base64,/, "");
    var dataBuffer = new Buffer(base64Data, 'base64');
    var timeStemp = new Date().getTime()
        fs.writeFile("./userAvator/" + timeStemp +".png", dataBuffer, function(err) {
            if(err){
            res.send(err);
        }else{
            pool.getConnection(function(err, connection) {
                connection.query('SELECT * FROM userImgApi WHERE userPhone = ?',[param.userPhone],function (err,result) {
                    if(err){
                        res.send({success:false,data:err,msg:'失败'});
                    }else if(result==false){ //为空的情况
                        connection.query('INSERT INTO userImgApi(userPhone,imgData) VALUES(?,?)',[param.userPhone,timeStemp],function (err,result) {
                            if(err){
                                res.send({success:false,data:err,msg:'失败'});
                                connection.release()
                            }else{
                                res.send({success:false,data:timeStemp,msg:'上传成功'});
                                connection.release()
                            }
                        })
                    }else{//不为空的情况
                        connection.query('UPDATE userImgApi SET imgData = ? WHERE userPhone = ?',[timeStemp,param.userPhone],function (err,result) {
                            if(err){
                                res.send({success:false,data:err,msg:'失败'});
                                connection.release()
                            }else{
                                res.send({success:false,data:timeStemp,msg:'上传成功'});
                                connection.release()
                            }
                        })
                    }
                })
            })
           
        }
    });
})

//更改用户头像 
router.post('/changeHeadImg', function(req, res, next){ //username
    var param = req.body;
    var timeStemp = new Date().getTime()
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.changeHeadImg,[param.headImg,param.userName,param.password],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'更改失败'});
                connection.release()
            }else if(result==false){
                res.send({success:false,msg:'更改失败,请检查密码'});
                connection.release()
            }else{
                res.send({success:true,msg:'更改成功'});
                connection.release()
            }
        })
    })
})

//更改用户信息
router.post('/updatedUserInfo', function(req, res, next){ //username
    var param = req.body;
    var timeStemp = new Date().getTime()
    pool.getConnection(function(err, connection) {
        connection.query(blogSQL.updatedUserInfo,[param.nickName,param.info,param.sex,param.name,param.password],function (err,result) {
            console.log(JSON.stringify(result)+'------------------------------------------------')
            if(err){
                res.send({success:false,data:err,msg:'更改失败'});
                connection.release()
            }else if(result.changedRows==0){
                res.send({success:false,msg:'更改失败,请检查密码'});
                connection.release()
            }else{
                res.send({success:true,msg:'更改成功'});
                connection.release()
            }
        })
    })
})

//获取用户头像
router.get('/getImg', function(req, res, next){ //username
    var param = req.query;
    var timeStemp = new Date().getTime()
    pool.getConnection(function(err, connection) {
        connection.query('SELECT imgData FROM userImgApi WHERE userPhone = ?',[param.userPhone],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'失败'});
                connection.release()
            }else if(result==false){
                res.send({success:true,msg:'该用户无头像'});
                connection.release()
            }else{
                fs.readFile('./userAvator/'+ result[0].imgData +'.png',"base64",function (err, data) {
                    if (err) {
                        return console.error(err);
                    }
                    res.send({success:true,data:data});
                    connection.release()
                })
            }
        })
    })
})

//取得服务器上面的图片
router.get('/img/*', function (req, res) {
    //__dirname就是执行这行代码的地址
    //path.resolve实际上就是cd __dirname 然后再 cd ..
    console.log(req.url)
    res.sendFile(path.resolve(__dirname, '..') + req.url);
    console.log("Request for " + req.url + " received.");
})

//上传图片接口 头像
router.post('/upImg', function(req, res, next){ //username
    var param = req.body;
    var imgData = req.body.imgData;
    var base64Data = imgData.replace(/^data:image\/\w+;base64,/, "");
    var dataBuffer = new Buffer(base64Data, 'base64');
    var timeStemp = new Date().getTime()
        fs.writeFile("./img/" + timeStemp +".png", dataBuffer, function(err) {
        if(err){
            res.send({success:false,data:'',msg:'上传失败'});
        }else{
           res.send({success:true,data:'http://dlhtx.zicp.vip:3000/img/'+timeStemp+'.png',msg:'上传成功'});
        }
    });
})


//爬虫接口
router.post('/pythonBt', function(req, res, next){ //username
    var param = req.body;
    pool.getConnection(function(err, connection) {
        connection.query('INSERT INTO bt(title,url,name) VALUES (?,?,?)',[param.title,param.url,param.name],function (err,result) {
            if(err){
                res.send({success:false,data:err,msg:'失败'});
                connection.release()
            }else{
                res.send({success:true,data:'添加成功'});
                connection.release()
            }
        })
    })
})

router.post('/testJson', function(req, res, next){ //username
    var param = req.body;
    console.log(req.body)
    res.send({success:true,data:req.body});
})

module.exports = router;
